

 
 /*
 Normalizing Information From sales_persons_table.
 Migrating Informtion to Sales_Persons  and Customer_SalesPerson_Relationship
 */
 
ALTER  PROC sp_migrate_sales_persons as 
BEGIN 
 	
 DECLARE @sales_persons_cursor CURSOR 
 DECLARE @sales_person_name varchar(50)
 DECLARE @deactivated int
 DECLARE @address_id int
 DECLARE @sales_person_id int
 DECLARE @customer_id int
 DECLARE @row_count int

 DECLARE @customer_salesPerson_relationship_cursor CURSOR
 DECLARE @sales_Order_cursor CURSOR
  



 --Retrieving Distinct Sales Persons From the sales_persons_table 
SET @sales_persons_cursor = CURSOR FOR
	SELECT DISTINCT SALES_PERSON_NAME, Deactivated, (SELECT TOP 1 Address_Id FROM sales_persons_table s2 WHERE s2.SALES_PERSON_NAME = s1.SALES_PERSON_NAME and s2.Address_id IS NOT NULL )[Address_Id]
	FROM sales_persons_table s1
	WHERE Address_id IS NOT NULL ORDER BY SALES_PERSON_NAME

 OPEN @sales_persons_cursor
 FETCH NEXT FROM @sales_persons_cursor INTO @sales_person_name, @deactivated, @address_id 
 WHILE @@FETCH_STATUS = 0
 BEGIN
	--Inserting the Sales Person into the Sales_Persons Table
	SELECT @ROW_COUNT = COUNT(*) FROM Sales_Persons WHERE SalesPerson_Name = @sales_person_name
	IF( @ROW_COUNT = 0 )
	BEGIN
		INSERT INTO Sales_Persons ( SalesPerson_Name, Deactivated, Address_Id ) VALUES ( @sales_person_name, @deactivated, @address_id ) 
		SET @sales_person_id = ( SELECT @@identity )		
		
		--Retriving the Customer IDs, for whom this person is acting as Sales Person
		 SET @customer_salesPerson_relationship_cursor = CURSOR FOR 
			SELECT DISTINCT (CUSTOMER_ID)[CUSTOMER_ID] FROM sales_persons_table WHERE SALES_PERSON_NAME = @sales_person_name
		
		 OPEN @customer_salesPerson_relationship_cursor
		 FETCH NEXT FROM @customer_salesPerson_relationship_cursor INTO @customer_id
		 WHILE @@FETCH_STATUS = 0
		 BEGIN
			--Establising relation between Sales Person and Customer
			INSERT INTO Customer_SalesPerson_Relationship ( SalesPerson_ID, Customer_ID, RelationshipStatus_Id ) VALUES ( @sales_person_id, @customer_id, 1 ) 
			FETCH NEXT FROM @customer_salesPerson_relationship_cursor INTO @customer_id
		 END
		 CLOSE @customer_salesPerson_relationship_cursor
		 DEALLOCATE @customer_salesPerson_relationship_cursor
	END
	FETCH NEXT FROM @sales_persons_cursor INTO @sales_person_name, @deactivated, @address_id
 END

 CLOSE @sales_persons_cursor
 DEALLOCATE @sales_persons_cursor
  --fetching the sales persons from sales order table if there is any sales person is not having relation ship with customer in sales_persons_table
 SET @sales_Order_cursor = CURSOR FOR SELECT Customer_Id, SO_Sales_Person from Sales_Order where SO_Sales_Person is not null and len(LTRIM(RTRIM(SO_Sales_Person))) > 0 and SO_Sales_Person in ( SELECT SalesPerson_Name FROM Sales_Persons )
 OPEN @sales_Order_cursor
 FETCH NEXT FROM @sales_Order_cursor INTO @customer_id, @sales_person_name
 WHILE @@FETCH_STATUS = 0
 BEGIN 
	SELECT @sales_Person_Id = SalesPerson_Id FROM Sales_Persons WHERE SalesPerson_Name = @sales_person_name
	SELECT @ROW_COUNT = count(*) FROM Customer_SalesPerson_Relationship WHERE SalesPerson_Id = @sales_Person_Id and Customer_Id = @customer_id 
	IF( @ROW_COUNT= 0 )
	BEGIN
		INSERT INTO Customer_SalesPerson_Relationship ( SalesPerson_ID, Customer_ID, RelationshipStatus_Id ) VALUES ( @sales_person_id, @customer_id, 1 ) 
	END
	FETCH NEXT FROM @sales_Order_cursor INTO @customer_id, @sales_person_name
 END
 CLOSE @sales_Order_cursor
 DEALLOCATE @sales_Order_cursor

 --fetching the sales persons from Quotation table if there is any sales person is not having relation ship with customer in sales_persons_table
 SET @sales_Order_cursor = CURSOR FOR SELECT Customer_Id, Q_Sales_Person from Quotation_table where Q_Sales_Person is not null and len(LTRIM(RTRIM(Q_Sales_Person))) > 0 and Q_Sales_Person in ( SELECT SalesPerson_Name FROM Sales_Persons )
 OPEN @sales_Order_cursor
 FETCH NEXT FROM @sales_Order_cursor INTO @customer_id, @sales_person_name
 WHILE @@FETCH_STATUS = 0
 BEGIN 
	SELECT @sales_Person_Id = SalesPerson_Id FROM Sales_Persons WHERE SalesPerson_Name = @sales_person_name
	SELECT @row_count = count(*) FROM Customer_SalesPerson_Relationship WHERE SalesPerson_Id = @sales_Person_Id and Customer_Id = @customer_id 
	IF( @row_count= 0 )
	BEGIN
		INSERT INTO Customer_SalesPerson_Relationship ( SalesPerson_ID, Customer_ID, RelationshipStatus_Id ) VALUES ( @sales_person_id, @customer_id, 1 ) 
	END
	FETCH NEXT FROM @sales_Order_cursor INTO @customer_id, @sales_person_name
 END
 CLOSE @sales_Order_cursor
 DEALLOCATE @sales_Order_cursor
--TO UPDATE THE SALES PERSON NAME IN SALES ORDER TABLE WITH UPPER CASE
 UPDATE sales_order SET SO_Sales_Person = UPPER(LTRIM(RTRIM(SO_Sales_Person))) WHERE LEN(LTRIM(RTRIM(SO_Sales_Person))) > 0
END



